package visiopuce.DAO;

import java.math.BigDecimal;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;

import visiopuce.HibernateUtil;
import visiopuce.objets.Analyse;
import visiopuce.objets.Secteur;

public class AnalyseDAOImpl extends GenericDAOImpl<Analyse, BigDecimal> implements AnalyseDAO {

	/** Constructeur priv� */
	private AnalyseDAOImpl() {
	}

	/** Instance unique non pr�initialis�e */
	private static AnalyseDAOImpl INSTANCE = null;

	/** Point d'acc�s pour l'instance unique du singleton */
	public static AnalyseDAO getInstance() {
		if (INSTANCE == null) {
			INSTANCE = new AnalyseDAOImpl();
		}
		return INSTANCE;
	}

	public List<Analyse> findAllASigner(int idSecteur, int idTypeAnalyse) {
		List<Analyse> analyses = null;
		String sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t right outer join t.personne p  WHERE p.idPersonne is not null ";
		sql += " and (r is null OR (r is not null and r.dateRendu is null))";
		sql += " and a is not null ";
		// sql += " and a is not null and a.dateTechnique is not null";
		// sql +=
		// " and (a is not null and (a.dateTechnique is not null and a.prelevement.dateReception is not null and a.prelevement.dateReception > '2014-09-20') ";
		// sql +=
		// " or a is not null and (a.dateTechnique is not null and a.prelevement.dateReception is not null and a.prelevement.dateReception > '2014-09-20') ) ";
		sql += " and a.typeAnalyse.idTypeAnalyse = :idTypeAnalyse";
		sql += " and t.secteur.idSecteur = :idSecteur order by t.dateReception ";

		Query query = HibernateUtil.getSession().createQuery(sql);
		// query.setMaxResults(300);

		query.setParameter("idTypeAnalyse", idTypeAnalyse);
		query.setParameter("idSecteur", idSecteur);
		analyses = findMany(query);
		return analyses;
	}

	public List<Analyse> getAnalysePuceReunion() {
		List<Analyse> analyses = null;
		String sql = "SELECT distinct a FROM Puce pu right outer join pu.analyse a right outer join a.prelevement t right outer join t.personne p  WHERE p.idPersonne is not null ";
		sql += " and (p is not null)";
		sql += " and a is not null ";
		// sql += " and a is not null and a.dateTechnique is not null";
		// sql +=
		// " and (a is not null and (a.dateTechnique is not null and a.prelevement.dateReception is not null and a.prelevement.dateReception > '2014-09-20') ";
		// sql +=
		// " or a is not null and (a.dateTechnique is not null and a.prelevement.dateReception is not null and a.prelevement.dateReception > '2014-09-20') ) ";
		sql += " and a.typeAnalyse.idTypeAnalyse = 3";
		sql += " and pu.dateRelecture is not null and pu.dateReunion is null and pu.aPasserEnReunion = true and dateScan > '2014-01-01'";

		Query query = HibernateUtil.getSession().createQuery(sql);

		analyses = findMany(query);
		return analyses;
	}

	public List<Analyse> findAllATechniquer(int idSecteur, int idTypeAnalyse) {
		List<Analyse> analyses = null;
		String sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t right outer join t.personne p  WHERE p.idPersonne is not null ";
		sql += " and (r is null OR r.dateRendu is not null)";
		sql += " and a is not null and a.dateTechnique is null and (a.prelevement.dateReception is not null and a.prelevement.dateReception > '2014-09-20')) or (a.prelevement.datePrelevement is not null and a.prelevement.datePrelevement > '2014-09-20')) ";
		sql += " and a.typeAnalyse.idTypeAnalyse = :idTypeAnalyse";
		sql += " and t.secteur.idSecteur = :idSecteur";
		Query query = HibernateUtil.getSession().createQuery(sql);
		query.setMaxResults(200);
		query.setParameter("idTypeAnalyse", idTypeAnalyse);
		query.setParameter("idSecteur", idSecteur);
		analyses = findMany(query);
		return analyses;
	}

	public List<Analyse> findAllByidPrelevement(Integer idPrelevement) {
		List<Analyse> analyses = null;
		String sql = "SELECT a FROM Analyse a WHERE a.prelevement.idPrelevement = :idPrelevement";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPrelevement", idPrelevement);
		analyses = findMany(query);
		return analyses;
	}

	public List<Analyse> findAllByidPersonne(Integer idPersonne) {
		List<Analyse> analyses = null;
		String sql = "SELECT a FROM Analyse a WHERE a.prelevement.personne.idPersonne = :idPersonne";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne);
		analyses = findMany(query);
		return analyses;
	}

	public List getAllUFExec() {
		String sql = "SELECT distinct a.ufExec FROM Analyse a ";
		Query query = HibernateUtil.getSession().createQuery(sql);
		List ufExec = query.list();
		return ufExec;
	}

	@Override
	public boolean isUrgent(Integer idAnalyse) {
		String sql = "SELECT  a.urgent FROM Analyse a WHERE idAnalyse = :idAnalyse";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idAnalyse", idAnalyse);
		boolean r = (boolean) query.uniqueResult();
		return r;
	}

	@Override
	public Secteur getSecteurByIdAnalyse(Integer idAnalyse) {
		Secteur secteur = null;
		if (idAnalyse != null) {
			String sql = null;
			Query query = null;
			sql = "SELECT a.prelevement.secteur FROM Analyse a WHERE a.idAnalyse = :idAnalyse";
			query = HibernateUtil.getSession().createQuery(sql).setParameter("idAnalyse", idAnalyse);
			secteur = (Secteur) query.uniqueResult();
		}
		return secteur;
	}

	@Override
	public Analyse getAnalyseByTypeAndNumeroPrelevement(int idType, String numeroDossier) {
		Analyse a = null;
		List<Analyse> analyses = null;
		String sql = "SELECT a FROM Analyse a WHERE a.prelevement.numeroPrelevement = :numeroDossier and a.typeAnalyse.idTypeAnalyse = :typeAnalyse";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numeroDossier", numeroDossier).setParameter("typeAnalyse", idType);
		analyses = findMany(query);
		if (analyses != null && analyses.size() > 0) {
			a = analyses.get(0);
		}
		return a;
	}

	@Override
	public Analyse getAnalyseByPersonneTypeAnalyse(Integer idPersonne, int typeAnalyse) {
		Analyse a = null;
		List<Analyse> analyses = null;
		String sql = "SELECT a FROM Analyse a WHERE a.prelevement.personne.idPersonne = :idPersonne and a.typeAnalyse.idTypeAnalyse = :typeAnalyse";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne).setParameter("typeAnalyse", typeAnalyse);
		analyses = findMany(query);
		if (analyses != null && analyses.size() > 0) {
			a = analyses.get(0);
		}
		return a;
	}

	@Override
	public List<Analyse> getListeADN(int annee) {
		List<Analyse> analyses = null;
		String sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t ";
		sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 ";
		sql += " and ((t.dateReception BETWEEN :annee AND :annee2) OR (t.datePrelevement BETWEEN :annee AND :annee2)) " + " and (r is null OR r.dateRendu is null) " + "order by t.numeroPrelevement ";

		Query query = HibernateUtil.getSession().createQuery(sql);
		// query.setMaxResults(300);

		query.setDate("annee", Date.valueOf(annee + "-01-01"));
		query.setDate("annee2", Date.valueOf(annee + 1 + "-01-01"));
		analyses = findMany(query);
		return analyses;
	}

	@Override
	public List<Analyse> getListeTravail(String travail, String analyse) {
		List<Analyse> analyses = null;
		String sql = "";
		
		// microsat
		if (analyse.contains("Microsat")) {
			sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t ";
			sql += " WHERE a.typeAnalyse.idTypeAnalyse = 6 ";
			sql += " and (t.dateReception > '2014-12-01' )";
		}
		// qPCR
		if (analyse.contains("qPCR")) {
			sql = "SELECT distinct a FROM RenduBiomol rbm right outer join rbm.resultat r right outer join r.analyse a right outer join a.prelevement t ";
			sql += " WHERE a.typeAnalyse.idTypeAnalyse = 4 ";
			sql += " and (t.dateReception > '2014-12-01' )";
		}
		// FISH NON PUCE------------------
		if (analyse.contains("FISH non puce")) {
			sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t ";
			sql += " WHERE a.typeAnalyse.idTypeAnalyse in (8,5,1,7,9) ";
			sql += " and (t.dateReception > '2014-12-01') ";
		}
		// FISH puce
		if (analyse.contains("FISH puce")) {
			sql = "SELECT distinct a FROM RenduFish rf right outer join rf.resultat r right outer join r.analyse a right outer join a.prelevement t ";
			sql += " WHERE a.typeAnalyse.idTypeAnalyse = 2 ";
		}
		// caryo
		if (analyse.contains("aryotype")) {
			sql = "SELECT distinct a FROM Resultat r right outer join r.analyse a right outer join a.prelevement t ";
			sql += " WHERE a.typeAnalyse.idTypeAnalyse in (1, 7 , 9) ";
			sql += " and (t.dateReception > '2014-12-01' )";
			
			if (analyse.contains("Caryotype Post")){
				sql += " and t.secteur.idSecteur = 2 ";
			}
			if (analyse.equals("Caryotype Pré")){
				sql += " and t.secteur.idSecteur = 1 ";
			}
			if (analyse.contains("Caryotype Foeto")){
				sql += " and t.secteur.idSecteur = 4 ";
			}
		}
		sql += " and (r is null OR r.dateRendu is null) ";

		if (analyse.contains("FISH") || analyse.contains("Microsat")) {
			if (travail.equals("A techniquer")) {
				sql += " and (a.dateTechnique is null and t.dateReception is not null and (r is null OR r.signe = false))";
			} else if (travail.equals("Résultat à rédiger")) {
				sql += " and (a.dateTechnique is not null and (r is null OR (r.signe = false and r.commentaire is null)))";
			} else if (travail.equals("A signer")) {
				sql += " and (a.dateTechnique is not null and (r is not null and r.signe = false) and r.commentaire is not null)";
			}
		}

		// -------------------------------------
		if (analyse.contains("aryotype")) {

			if (travail.equals("A lire") || travail.equals("A dénaturer") || travail.equals("A étaler") || travail.equals("A choquer")) {
				sql += " and (a.dateTechnique is null and t.dateReception is not null and (r is null OR r.signe = false)) ";
			} else if (travail.equals("Résultat à rédiger") || travail.equals("A relire")) {
				sql += " and (a.dateTechnique is not null and (r is null OR (r.signe = false and r.commentaire is null)))";
			} else if (travail.equals("A signer")) {
				sql += " and (a.dateTechnique is not null and (r is not null and r.signe = false))";
			}
		}
		if (analyse.contains("qPCR")) {
			if (travail.equals("A techniquer")) {
				sql += " and (a.dateTechnique is null and t.dateReception is not null and (r is null OR r.signe = false))";
			} else if (travail.equals("Résultat à rédiger")) {
				sql += " and (a.dateTechnique is not null and (r is null OR (r.signe = false and r.commentaire is null)) and (rbm is null or (rbm.dateRedaction is null and rbm.dateVerification is null)))";
			} else if (travail.equals("A relire")) {
				sql += " and (a.dateTechnique is not null and (r is null OR (r.signe = false and r.commentaire is null)) and (rbm is not null and rbm.dateRedaction is not null and rbm.dateVerification is null))";
			} else if (travail.equals("A signer")) {
				sql += " and (a.dateTechnique is not null and (r is not null and r.signe = false) and (rbm is not null and rbm.dateRedaction is not null and rbm.dateVerification is not null))";
			}
		}
		if (analyse.contains("uce") && !analyse.contains("FISH")) {
			// puce

			if (travail.equals("A techniquer")) {
				sql = "SELECT distinct a FROM Puce pu right outer join pu.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a ";
				sql += " and a.dateTechnique is null and t.dateReception is not null ";
			} else if (travail.equals("A lire")) {
				sql = "SELECT distinct a FROM Puce pu right outer join pu.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a ";
				sql += " and (a.dateTechnique is not null and (pu is not null and pu.dateAnnotation is null))";
			} else if (travail.equals("A relire")) {
				sql = "SELECT distinct a FROM Puce pu right outer join pu.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a ";
				sql += " and (a.dateTechnique is not null and (pu is not null and pu.dateAnnotation is not null and pu.dateRelecture is null))";
			} else if (travail.equals("Résultat à rédiger")) {
				sql = "SELECT distinct a FROM Puce pu, RenduPuce rp right outer join rp.resultat r right outer join r.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a ";
				sql += " and a.dateTechnique is not null and r.signe = false and r.commentaire is null and pu is not null and pu.dateAnnotation is not null and pu.dateRelecture is not null"
						+ " and (pu.aPasserEnReunion = false or (pu.aPasserEnReunion = true and pu.dateReunion is not null)) ";
			} else if (travail.equals("A passer en réunion")) {
				sql = "SELECT distinct a FROM Puce pu, RenduPuce rp right outer join rp.resultat r right outer join r.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a and rp.puce = pu or rp is null";
				sql += " and a.dateTechnique is not null and (r is null OR (r.signe = false and r.commentaire is null)) and pu is not null and pu.dateAnnotation is not null and pu.dateRelecture is not null and pu.aPasserEnReunion = true and pu.dateReunion is null ";
			} else if (travail.equals("A signer")) {
				sql = "SELECT distinct a FROM Puce pu, RenduPuce rp right outer join rp.resultat r right outer join r.analyse a right outer join a.prelevement t ";
				sql += " WHERE a.typeAnalyse.idTypeAnalyse = 3 and pu.analyse = a ";
				sql += " and a.dateTechnique is not null and (pu.aPasserEnReunion = false or (pu.aPasserEnReunion = true and pu.dateReunion is not null) and (r is not null and r.signe = false))";
				
			}
			if (analyse.equals("Puce prénatale")){
				sql += " and t.secteur.idSecteur = 3 ";
			}
			if (analyse.equals("Puce postnatale")){
				sql += " and t.secteur.idSecteur = 8 ";
			}
			if (analyse.contains("Puce foeto")){
				sql += " and t.secteur.idSecteur = 9 ";
			}
			sql += " and (t.dateReception > '2013-12-01' ) ";
		}
		sql += " order by t.numeroPrelevement ";
		Query query = HibernateUtil.getSession().createQuery(sql);
		// query.setMaxResults(300);

		analyses = findMany(query);

		// FISH NON PUCE------------------
		if (analyse.contains("FISH") && !analyse.contains("puce")) {
			List<Analyse> newListe = new ArrayList<Analyse>();
			for (Analyse analyse2 : analyses) {
				int typeAna = analyse2.getTypeAnalyse().getIdTypeAnalyse();
				if (typeAna == 8 || typeAna == 5) {
					newListe.add(analyse2);
				}
				if (typeAna == 1 || typeAna == 7 || typeAna == 9) {
					String sql2 = "Select c.valeur from Champ c where idObjet like 'Ana-" + analyse2.getIdAnalyse() + "' and type like 'btnPlusFish' ";
					Query query2 = HibernateUtil.getSession().createQuery(sql2);
					String btnPlusFish = (String) query2.uniqueResult();
					if (btnPlusFish != null && btnPlusFish.equals("true")) {
						newListe.add(analyse2);
					}
				}
			}
			analyses = newListe;
		}

		if (analyse.contains("aryotype")) {
			List<Analyse> newListe = new ArrayList<Analyse>();
			for (Analyse analyse2 : analyses) {
				if (travail.equals("A signer")) {
					newListe.add(analyse2);
				} else {
					String sql2 = "Select c.valeur from Champ c where idObjet like 'Ana-" + analyse2.getIdAnalyse() + "' and type like 'comboTechChoc' ";
					Query query2 = HibernateUtil.getSession().createQuery(sql2);
					String comboTechChoc = (String) query2.uniqueResult();

					String sql3 = "Select c.valeur from Champ c where idObjet like 'Ana-" + analyse2.getIdAnalyse() + "' and type like 'comboTechEtalement' ";
					Query query3 = HibernateUtil.getSession().createQuery(sql3);
					String comboTechEtalement = (String) query3.uniqueResult();

					String sql4 = "Select c.valeur from Champ c where idObjet like 'Ana-" + analyse2.getIdAnalyse() + "' and type like 'comboTechDenat' ";
					Query query4 = HibernateUtil.getSession().createQuery(sql4);
					String comboTechDenat = (String) query4.uniqueResult();

					String sql5 = "Select c.valeur from Champ c where idObjet like 'Ana-" + analyse2.getIdAnalyse() + "' and type like 'tResolutionCaryo' ";
					Query query5 = HibernateUtil.getSession().createQuery(sql5);
					String tResolutionCaryo = (String) query5.uniqueResult();

					if (analyse2.getDateTechnique() == null) {
						if (travail.equals("A choquer") && (comboTechChoc == null || (comboTechChoc != null && comboTechChoc.equals("0")))) {
							newListe.add(analyse2);
						}
						if (travail.equals("A étaler") && (comboTechChoc != null && !comboTechChoc.equals("0")) && (comboTechEtalement == null || (comboTechEtalement != null && comboTechEtalement.equals("0")))) {
							newListe.add(analyse2);
						}
						if (travail.equals("A dénaturer") && (comboTechEtalement != null && !comboTechEtalement.equals("0")) && (comboTechDenat == null || (comboTechDenat != null && comboTechDenat.equals("0")))) {
							newListe.add(analyse2);
						}
						if (travail.equals("A lire") && (comboTechDenat != null && !comboTechDenat.equals("0")) && (tResolutionCaryo == null || (tResolutionCaryo != null && tResolutionCaryo.equals("NULL")))) {
							newListe.add(analyse2);
						}
					} else {
						if (travail.equals("A relire") && (comboTechDenat != null && !comboTechDenat.equals("0")) && (tResolutionCaryo == null || (tResolutionCaryo != null && tResolutionCaryo.equals("NULL")))) {
							newListe.add(analyse2);

						}
						if (travail.equals("Résultat à rédiger") && (comboTechDenat != null && !comboTechDenat.equals("0")) && (tResolutionCaryo != null && !tResolutionCaryo.equals("NULL"))) {
							newListe.add(analyse2);
						}
					}

				}

			}
			analyses = newListe;
		}
		return analyses;
	}
}